from typing import List
from numpy.core.fromnumeric import put, size
import pymysql
import xlrd
import random
import pandas as pd
import datetime


# 补全数据
# df = pd.read_excel("supermarket.xls")
# list = ['广州','佛山','东莞','杭州','北京']

# for i in df.index:
#     t = random.randint(0,4)
#     price_cat = random.randint(3,10)
#     price = random.randint(11,30)
#     kucun = random.randint(50,100)
#     kucun_yujin = random.randint(10,20)
#     df['产地'][i+1] = list[t]
#     df['会员价格'][i+1] = price_cat
#     df['价格'][i+1] = price
#     df['商品库存'][i+1] = kucun
#     df['预警库存'][i+1] = kucun_yujin
#     df['生产时间'][i+1] = datetime.datetime.now() 
#     df['到期时间'][i+1] = datetime.datetime.now() + datetime.timedelta(hours=720)  
# df['规格'].fillna('200ml',inplace=True)
# df.to_excel('supermarket.xls')

# 连接数据库
goods = xlrd.open_workbook("supermarket.xls")
database = pymysql.connect(host="localhost", user = "root", passwd = "13751425366",db = "market", charset="utf8")
worksheet = goods.sheet_by_index(0)
print(worksheet)
# 插入数据库

# 获取游标对象，用于逐行遍历数据库数据
cursor = database.cursor()

query = """INSERT INTO bus_goods (id, goodsname, produceplace, size, goodspackage, productcode, promitcode, description, memberprice, price, providerid, available, number, dangernum, goodsimg, productiondate, expiration, opretor) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

for r in range(1,worksheet.nrows):
    id = r
    goodsname = worksheet.cell(r,1).value
    produceplace = worksheet.cell(r,2).value
    size1 = worksheet.cell(r,3).value
    goodspackage = worksheet.cell(r,4).value
    productcode = worksheet.cell(r,5).value
    promitcode = worksheet.cell(r,6).value
    description = worksheet.cell(r,7).value
    memberprice = worksheet.cell(r,8).value
    price = worksheet.cell(r,9).value
    providerid = worksheet.cell(r,10).value
    available = worksheet.cell(r,11).value
    number = worksheet.cell(r,12).value
    dangernum = worksheet.cell(r,13).value
    goodsimg = worksheet.cell(r,14).value
    # productiondate = worksheet.cell(r,15).value
    productiondate = datetime.datetime.now()
    # expiration = worksheet.cell(r,16).value
    expiration = datetime.datetime.now() + datetime.timedelta(hours=720)
    opretor = worksheet.cell(r,17).value
    
    values = (str(id), goodsname, produceplace, size1, goodspackage,productcode, promitcode, description, memberprice, price, providerid, available, number, dangernum, goodsimg, productiondate, expiration, opretor)
    cursor.execute(query, values)

cursor.close()

database.commit()

database.close()

